package com.zxj.geekstudy.mysql.service;

import com.mysql.jdbc.StringUtils;
import com.zxj.geekstudy.mysql.annotation.DynamicDS;
import com.zxj.geekstudy.mysql.db.HikariDB;
import com.zxj.geekstudy.spring.db.po.Student;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

@Service
public class StudentService {

    @DynamicDS(value = "dsMaster")
    public int insert(String name, BigDecimal score) throws Exception {
        Connection connection = HikariDB.getConnection();
        System.out.println("StudentService.insert()使用数据源" + connection.getMetaData().getURL());
        PreparedStatement ps = null;
        try {
            String sql = "INSERT INTO `student` (`name`, `score`) VALUES ( ?, ? )";
            ps = connection.prepareStatement(sql);
            ps.setString(1, name);
            ps.setBigDecimal(2, score);
            int i = ps.executeUpdate();
            return i;
        } finally {
            if (ps != null) {
                ps.close();
            }
        }
    }

    @DynamicDS(value = "dsMaster")
    public int update(String name, BigDecimal score) throws Exception {
        if (StringUtils.isEmptyOrWhitespaceOnly(name) || score == null) {
            return 0;
        }
        Connection connection = HikariDB.getConnection();
        System.out.println("StudentService.update()使用数据源" + connection.getMetaData().getURL());
        PreparedStatement ps = null;
        try {
            String sql = "UPDATE `student` SET `score`= ? WHERE (`name` = ?)";
            ps = connection.prepareStatement(sql);
            ps.setBigDecimal(1, score);
            ps.setString(2, name);
            return ps.executeUpdate();
        } finally {
            if (ps != null) {
                ps.close();
            }
        }
    }

    @DynamicDS(value = "dsMaster")
    public int delete(String name) throws Exception {
        if (StringUtils.isEmptyOrWhitespaceOnly(name)) {
            return 0;
        }
        Connection connection = HikariDB.getConnection();
        System.out.println("StudentService.delete()使用数据源" + connection.getMetaData().getURL());
        PreparedStatement ps = null;
        try {
            String sql = "DELETE FROM `student` WHERE (`name` = ?)";
            ps = connection.prepareStatement(sql);
            ps.setString(1, name);
            return ps.executeUpdate();
        } finally {
            if (ps != null) {
                ps.close();
            }
        }
    }

    @DynamicDS(value = "dsSlave")
    public List<Student> query(String name) throws Exception {
        Connection connection = HikariDB.getConnection();
        System.out.println("StudentService.query()使用数据源" + connection.getMetaData().getURL());
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            String sql = "SELECT * FROM `student`";
            if (!StringUtils.isEmptyOrWhitespaceOnly(name)) {
                sql += " WHERE name = ?";
            }
            ps = connection.prepareStatement(sql);
            if (!StringUtils.isEmptyOrWhitespaceOnly(name)) {
                ps.setString(1, name);
            }
            resultSet = ps.executeQuery();
            List<Student> list = new ArrayList<>();
            // 5.处理结果集（针对查询）
            while (resultSet.next()) {
                // 一次循环处理一行，一行转换成一个对象
                Student student = new Student(resultSet.getInt(1), resultSet.getString(2), resultSet.getBigDecimal(3));
                list.add(student);// 添加到集合
            }
            return list;
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
    }
}
